﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;
using CNative.Utilities;
using System.IO;
using System.Data.Common;

namespace CNative.Dapper.Utils
{
    internal class MySqlProvider : BaseProvider
    {
        public MySqlProvider(IDbHelper _db) : base(_db)
        {
        }
        #region 关键字
        /// <summary>
        /// 数据库提供程序名字
        /// </summary>
        public override string ProviderName
        {
            get
            {
                return "MySql.Data.MySqlClient";
            }
        }
        public override string ProviderNameFactory
        {
            get
            {
                return "MySql.Data.MySqlClient.MySqlClientFactory";
            }
        }
        /// <summary>
        /// 数据库类型
        /// </summary>
        public override DatabaseType DBType { get { return DatabaseType.MySql; } }
        /// <summary>
        /// 参数关键字 ?
        /// </summary>
        public override string ParamKeyword
        {
            get
            {
                return "?";
            }
        }
        /// <summary>
        /// 关键字前缀 `
        /// </summary>
        public override string SuffixLeft
        {
            get
            {
                return "`";
            }
        }
        /// <summary>
        /// 关键字后缀 `
        /// </summary>
        public override string SuffixRigh
        {
            get
            {
                return "`";
            }
        }
        #endregion

        #region DbProviderFactory
        public override string GetSchemaTableName(Type tableType)
        {
            var tb = Funs.GetDbTableInfo(dbHelper, tableType);
            if (tb == null)
            {
                return "";
            }
            return tb.Schema.IsNullOrEmpty() ? tb.TableName : tb.Schema + "." + tb.TableName;
        }
        #endregion

        #region GetDbParameter
        ///// <summary>
        ///// 获取DbParameter
        ///// </summary>
        ///// <param name="dbType">数据库类型</param>
        ///// <returns></returns>
        //public override IDataParameter GetDbParameter(string propName, object val, string paramSuffix = "exp_",
        //    DbTableInfo tb = null, ParameterDirection direction = ParameterDirection.Input, DbType valDBType = DbType.String)
        //{
        //    IDataParameter para = null;
        //    //--------------------------------------------------------------------------------------------------------------
        //    var frets = FillerParameter(propName, val, paramSuffix);
        //    if (frets.Item1)
        //    {
        //        return para;
        //    }
        //    var parameterName = frets.Item2;
        //    val = frets.Item3;
        //    //--------------------------------------------------------------------------------------------------------------
        //    #region MySql
        //    if (direction == ParameterDirection.Output)
        //    {
        //        para = new MySql.Data.MySqlClient.MySqlParameter()
        //        {
        //            ParameterName = parameterName,
        //            Value = val,
        //            DbType = valDBType,
        //            Direction = direction
        //        };
        //        return para;
        //    }

        //    para = new MySql.Data.MySqlClient.MySqlParameter()
        //    {
        //        ParameterName = parameterName,
        //        Value = val,
        //    };
        //    if (tb != null && tb.TableInfo != null)
        //    {
        //        var dtinfo = tb.TableInfo.Find(f => f.Name.Equals(propName.Trim(), StringComparison.OrdinalIgnoreCase));
        //        if (dtinfo != null)
        //        {
        //            try
        //            {
        //                (para as MySql.Data.MySqlClient.MySqlParameter).MySqlDbType = MySqlTypeString2SqlType(dtinfo.Type);// dtinfo.Type.ToEnum<MySqlDbType>();
        //                if (dtinfo.MaxLength > 0 && dtinfo.MaxLength < int.MaxValue)
        //                    (para as MySql.Data.MySqlClient.MySqlParameter).Size = (int)dtinfo.MaxLength;
        //            }
        //            catch { }
        //        }
        //    }
        //    #endregion
        //    return para;
        //}
        //private MySql.Data.MySqlClient.MySqlDbType MySqlTypeString2SqlType(string sqlTypeString)
        //{
        //    var dbType = MySql.Data.MySqlClient.MySqlDbType.String; //默认为Object
        //    sqlTypeString = sqlTypeString.Trim().ToLower();
        //    #region switch (sqlTypeString)
        //    //switch (sqlTypeString)
        //    //{
        //    //    case "int":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Int32;
        //    //        break;
        //    //    case "varchar":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
        //    //        break;
        //    //    case "bit":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Bit;
        //    //        break;
        //    //    case "datetime":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.DateTime;
        //    //        break;
        //    //    case "date":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Date;
        //    //        break;
        //    //    case "time":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Time;
        //    //        break;
        //    //    case "datetime2":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.DateTime;
        //    //        break;
        //    //    case "datetimeoffset":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Timestamp;
        //    //        break;
        //    //    case "decimal":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Decimal;
        //    //        break;
        //    //    case "udt":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Timestamp;
        //    //        break;
        //    //    case "float":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Float;
        //    //        break;
        //    //    case "image":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Binary;
        //    //        break;
        //    //    case "money":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Double;
        //    //        break;
        //    //    case "ntext":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.LongText;
        //    //        break;
        //    //    case "nvarchar":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarString;
        //    //        break;
        //    //    case "smalldatetime":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Date;
        //    //        break;
        //    //    case "smallint":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.UInt16;
        //    //        break;
        //    //    case "text":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Text;
        //    //        break;
        //    //    case "bigint":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Int64;
        //    //        break;
        //    //    case "binary":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Binary;
        //    //        break;
        //    //    case "char":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
        //    //        break;
        //    //    case "nchar":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
        //    //        break;
        //    //    case "numeric":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Decimal;
        //    //        break;
        //    //    case "real":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Float;
        //    //        break;
        //    //    case "smallmoney":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Double;
        //    //        break;
        //    //    case "timestamp":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Timestamp;
        //    //        break;
        //    //    case "tinyint":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Bit;
        //    //        break;
        //    //    case "guid":
        //    //    case "uniqueidentifier":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.Guid;
        //    //        break;
        //    //    case "varbinary":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarBinary;
        //    //        break;
        //    //    case "json":
        //    //        dbType = MySql.Data.MySqlClient.MySqlDbType.JSON;
        //    //        break;
        //    //    default:
        //    //        dbType = sqlTypeString.ToEnum<MySql.Data.MySqlClient.MySqlDbType>();
        //    //        break;
        //    //}
        //    #endregion
        //    var parameterType = GetParameterTypeName(sqlTypeString);
        //    dbType = parameterType.ToEnum<MySql.Data.MySqlClient.MySqlDbType>();
        //    return dbType;
        //}
        #endregion
        #region MappingTypes
        /// <summary>
        /// 类型映射
        /// sqlTypeName，CsharpType，ParameterType
        /// </summary>
        public override List<Tuple<string, string, string>> MappingTypes
        {
            get
            {
                return new List<Tuple<string, string, string>>()
                    {
                        Tuple.Create("bit", "bool", "Bit"),
                        Tuple.Create("bigint", "long", "Int64"),
                        Tuple.Create("tinyint", "byte", "Bit"),
                        Tuple.Create("datetime", "DateTime", "Datetime"),
                        Tuple.Create("date", "DateTime", "Date"),
                        Tuple.Create("smalldatetime", "DateTime", "Date"),
                        Tuple.Create("time", "DateTime", "Time"),
                        Tuple.Create("timestamp", "string", "Timestamp"),
                        Tuple.Create("datetime2", "DateTime", "DateTime"),
                        Tuple.Create("datetimeoffset", "DateTime", "Timestamp"),
                        Tuple.Create("udt", "DateTime", "Timestamp"),
                        Tuple.Create("decimal", "decimal", "Decimal"),
                        Tuple.Create("money", "double", "Double"),
                        Tuple.Create("smallmoney", "double", "Double"),
                        Tuple.Create("binary", "byte[]", "Binary"),
                        Tuple.Create("blob", "byte[]", "Blob"),
                        Tuple.Create("longblob", "byte[]", "LongBlob"),
                        Tuple.Create("double", "double", "Double"),
                        Tuple.Create("float", "decimal", "Float"),
                        Tuple.Create("int", "int", "Int32"),
                        Tuple.Create("smallint", "short", "UInt16"),
                        Tuple.Create("image", "byte[]", "Binary"),
                        Tuple.Create("numeric", "decimal", "Decimal"),
                        Tuple.Create("real", "decimal", "Float"),
                        Tuple.Create("text", "string", "Text"),
                        Tuple.Create("ntext", "string", "LongText"),
                        Tuple.Create("longtext", "string", "LongText"),
                        Tuple.Create("char", "string", ",VarChar"),
                        Tuple.Create("nchar", "string", ",VarChar"),
                        Tuple.Create("varchar", "string", ",VarChar"),
                        Tuple.Create("nvarchar", "string", ",VarString"),
                        Tuple.Create("guid", "Guid", ",Guid"),
                        Tuple.Create("uniqueidentifier", "Guid", ",Guid"),
                        Tuple.Create("varbinary", "byte[]", ",VarBinary")
                    };
            }
        }

        /// <summary>
        /// 获取库类型
        /// </summary>
        /// <param name="csharpTypeName"></param>
        /// <returns></returns>
        public override string GetDbTypeName(string csharpTypeName)
        {
            if (csharpTypeName == Constants.ByteArrayType.Name)
                return "blob";
            csharpTypeName = CheckCsharpTypeName(csharpTypeName);
            var mappings = this.MappingTypes?.Where(it => it.Item2.Equals(csharpTypeName, StringComparison.CurrentCultureIgnoreCase)).ToList();
            if (mappings != null && mappings.Count > 0)
                return mappings.First().Item1;
            else
                return "varchar";
        }
        /// <summary>
        /// 获取sql Parameter Type
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public override string GetParameterTypeName(string dbTypeName)
        {
            var mappings = this.MappingTypes?.Where(it => it.Item1.Equals(dbTypeName, StringComparison.CurrentCultureIgnoreCase));
            return !mappings.IsNullOrEmpty_() ? mappings.First().Item3 : "VarChar";
        }
        #endregion
        #region Sql Fun Templet
        /// <summary>
        /// SQL NVL() 从两个表达式返回一个非 null 值	函数
        /// </summary>
        /// <param name="check_expression">将被检查是否为 NULL的表达式。可以是任何类型的。</param>
        /// <param name="replacement_value">在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。</param>
        /// <returns>如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value</returns>
        public override string SQL_NVL(string check_expression, string replacement_value)
        {
            return $"IFNULL({check_expression},{replacement_value})";
        }
        /// <summary>
        /// 获取数据库时间函数
        /// </summary>
        public override string SqlDateNow
        {
            get
            {
                return "sysdate()"; //" NOW() ";
            }
        }
        /// <summary>
        /// 获取当前时间sql语句
        /// </summary>
        public override string FullSqlDateNow
        {
            get
            {
                return "select sysdate()";
            }
        }

        /// <summary>
        /// SQL UCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为大写</returns>
        public override string SQL_UCASE(string column_name)
        {
            return $"UPPER({column_name})";
        }

        /// <summary>
        /// SQL LCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为小写</returns>
        public override string SQL_LCASE(string column_name)
        {
            return $"LOWER({column_name})";
        }

        /// <summary>
        /// SQL LEFT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_LEFT(string str, int length)
        {
            return $"LEFT({str},{length})";
        }
        /// <summary>
        /// SQL RIGHT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_RIGHT(string str, int length)
        {
            return $"RIGHT({str},{length})";
        }
        /// <summary>
        /// SQL SUBSTR(s, start, length) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>从字符串 str 的 start 位置截取长度为 length 的子字符串</returns>
        public override string SQL_SUBSTR(string str, int start, int length)
        {
            return $"SUBSTR({str},{1 + start},{length})";
        }
        /// <summary>
        /// SQL TRIM(s) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>去掉字符串 str 开始和结尾处的空格</returns>
        public override string SQL_TRIM(string str)
        {
            return $"TRIM({str})";
        }
        /// <summary>
        /// SQL CAST() 转换数据类型	函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <param name="type">数据类型</param>
        /// <returns>转换数据类型</returns>
        public override string SQL_CAST(string column_name, string type)
        {
            return $"CAST({column_name} AS {type})";
        }

        public override string SQL_ToInt32(string column_name)
        {
            return SQL_CAST(column_name, "SIGNED");
        }

        public override string SQL_ToInt64(string column_name)
        {
            return SQL_CAST(column_name, "SIGNED");
        }
        public override string SQL_ToDouble(string column_name)
        {
            return SQL_CAST(column_name, "DECIMAL(18,4)");
        }
        /// <summary>转换高精度的十进制数（一般用于货币）</summary>
        /// <param name="column_name"></param>
        /// <param name="d">小数位数</param>
        /// <returns></returns>
        public override string SQL_ToDecimal(string column_name, int d)
        {
            if (d < 0 || d > 10) d = 4;
            return SQL_CAST(column_name, "DECIMAL(18, " + d + ")");
        }

        public override string SQL_ToBool(string column_name)
        {
            return SQL_CAST(column_name, "SIGNED");
        }

        public override string SQL_ToVarchar(string column_name)
        {
            return SQL_CAST(column_name, "CHAR");
        }

        public override string SQL_ToGuid(string column_name)
        {
            return SQL_CAST(column_name, "CHAR");
        }

        public override string SQL_ToDate(string column_name)
        {
            return SQL_CAST(column_name, "DATETIME");
        }

        public override string SQL_ToDateShort(string column_name)
        {
            return SQL_CAST(column_name, "DATE");
        }

        public override string SQL_ToTime(string column_name)
        {
            return SQL_CAST(column_name, "TIME");
        }
        #endregion

        #region SqlTemplet
        /// <summary>
        /// 一般查询语句模板
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fieldsstr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public override string GetSelectSQL(String tbname, string fieldsstr, string orderbyStr, int top = 0)
        {
            var sqlMeta = @"SELECT " + fieldsstr + " FROM " + tbname + " WHERE {0} " + orderbyStr + (top > 0 ? " limit 0, " + top + " " : "");
            return sqlMeta;
        }
        /// <summary>
        /// 分页查询语句模板
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="fieldsstr"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="page">当前页面</param>
        /// <param name="nums">每页记录数</param>
        /// <returns></returns>
        public override string GetSelectPageSQL(string tbName, string fieldsstr, string whereStr, string orderbyStr, int page = 1, int nums = 25)
        {
            if (page < 1) page = 1;
            if (nums < 1) nums = 10;
            var m = (page - 1) * nums;// + 1;
            var n = nums;// page * nums;

            var sqlMeta = $"SELECT {fieldsstr} FROM {tbName} WHERE {whereStr } {orderbyStr} limit {m},{n}";
            return sqlMeta;
        }
        /// <summary>
        /// MERGE 合并语句模板
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="tbname"></param>
        /// <returns></returns>
        public override string GetMergeSql(String tbname)
        {
            var sqlMeta = @"MERGE " + tbname + " AS [target]  USING(SELECT 1 AS ____Id_____) AS source ON {0}  WHEN MATCHED THEN {1}  WHEN NOT MATCHED THEN {2};";

            return sqlMeta;
        }

        /// <summary>
        /// 更新语句模板
        /// "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ")
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetUpdateSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ");

            return sqlMeta;
        }
        /// <summary>
        /// 插入语句模板
        /// "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})"
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetInsertSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})";

            return sqlMeta;
        }
        /// <summary>
        /// 统计数量语句模板
        /// $"SELECT Count(0) FROM {fromStr} WHERE {0}";
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public override string GetCountSql(String fromStr, string fieldName = "")
        {
            var sqlMeta = "SELECT Count(" + (fieldName.IsNullOrEmpty() ? "0" : fieldName) + ") FROM " + fromStr + " WHERE {0}";

            return sqlMeta;
        }
        /// <summary>
        /// GetInsertSelectSql
        /// INSERT INTO " + insertTable + " ({0}) " + " SELECT {1} FROM " + selectTable + "  WHERE {2} 
        /// </summary>
        /// <param name="insertTable"></param>
        /// <param name="selectTable"></param>
        /// <param name="_dbType"></param>
        /// <returns></returns>
        public override string GetInsertSelectSql(String insertTable, String selectTable)
        {
            var sqlMeta = "INSERT INTO " + insertTable + " ({0}) " +
                            " SELECT {1} FROM " + selectTable + "  WHERE {2} ";

            return sqlMeta;
        }
        #endregion

        #region WithNextSequence
        /// <summary>
        /// 下个序列脚本
        /// </summary>
        /// <param name="sequenceName"></param>
        /// <returns></returns>
        public override string WithNextSequence(string sequenceName = null)
        {
            if (string.IsNullOrEmpty(sequenceName))
            {
                sequenceName = "default_seq";
            }
            return $"nextval('{sequenceName}')";
        }
        #endregion

        #region DML CodeFirst
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        public override string GetDataBaseSql
        {
            get
            {
                return "SHOW DATABASES";
            }
        }
        /// <summary>
        /// 获取数据库中的所有表
        /// </summary>
        public override string GetTableInfoListSql
        {
            get
            {
                return @"select TABLE_NAME as name,TABLE_COMMENT as Description from information_schema.tables
                         where  TABLE_SCHEMA='{0}'  AND TABLE_TYPE='BASE TABLE'";
            }
        }
        /// <summary>
        /// 获取数据库中的所有视图
        /// </summary>
        public override string GetViewInfoListSql
        {
            get
            {
                return @"select TABLE_NAME as name,TABLE_COMMENT as Description from information_schema.tables
                         where  TABLE_SCHEMA='{0}' AND TABLE_TYPE='VIEW'";
            }
        }
        /// <summary>
        /// 得到一个表的所有列信息
        /// </summary>
        public override string GetColumnInfosByTableNameSql
        {
            get
            {
                return @"SELECT
                        TABLE_NAME as TableName, 
                        CONCAT(table_schema, '.', table_name) as  `table_name`,
		                ORDINAL_POSITION AS `ColumnId`,
                        column_name AS `Name`,
                        CASE WHEN  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1)='' THEN COLUMN_TYPE ELSE  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1) END   AS `Type`,
                        CAST(SUBSTRING(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)+1,LOCATE(')',COLUMN_TYPE)-LOCATE('(',COLUMN_TYPE)-1) AS signed) AS MaxLength,
                        column_default  AS  `DefaultValue`,
                        column_comment  AS  `Description`,
                        CASE WHEN COLUMN_KEY = 'PRI'
                        THEN true ELSE false END AS `IsPrimaryKey`,
                        CASE WHEN EXTRA='auto_increment' THEN true ELSE false END as IsIdentity,
                        CASE WHEN is_nullable = 'YES'
                        THEN true ELSE false END AS `IsNullable`,
                        numeric_scale as Scale,
                        numeric_scale as DecimalDigits,
                        COLUMN_TYPE as DbFullType
                        FROM
                        Information_schema.columns where TABLE_NAME='{1}' and  (TABLE_SCHEMA='{0}' OR ''='{0}') ORDER BY ORDINAL_POSITION;";
            }
        }
        #endregion

        #region BulkCopyData
        /// <summary>  
        /// 批量插入功能  
        /// </summary>  
        public override bool BulkCopyData(DataTable table, string destinationTableName = null, int? bulkCopyTimeout = null)
        {
            if (table == null || table.Rows.Count == 0) return false;
            if (string.IsNullOrEmpty(destinationTableName))
                destinationTableName = table.TableName;
            table.TableName = destinationTableName;

            var sqle = dbHelper.CreateSqlEntity();
            sqle.Sql = GenerateInserSql(sqle, table);
            if (sqle.Sql.IsNullOrEmpty_())
                return false;
            return dbHelper.Execute(sqle);

            //var cnn = (dbHelper as DbHelper).GetDbConnection();
            //string tmpPath = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "UpTemp");
            //try
            //{
            //    if (!Directory.Exists(tmpPath))
            //        Directory.CreateDirectory(tmpPath);
            //    tmpPath = Path.Combine(tmpPath, table.TableName + "_Temp.csv");//csv文件临时目录

            //    string csv = DataTableToCsv(table);
            //    File.WriteAllText(tmpPath, csv);

            //    var columns = table.Columns.Cast<DataColumn>().Select(_columns => _columns.ColumnName).ToList();
            //    dynamic bulk = FastReflection.FastInstance("MySql.Data.MySqlClient.MySqlBulkLoader", cnn);
            //    if (bulk != null)
            //    {
            //        bulk.FieldTerminator = ",";
            //        bulk.FieldQuotationCharacter = '"';
            //        bulk.EscapeCharacter = '"';
            //        bulk.LineTerminator = "\r\n";
            //        bulk.FileName = tmpPath;
            //        bulk.NumberOfLinesToSkip = 0;
            //        bulk.TableName = table.TableName;

            //        bulk.Columns.AddRange(columns);//根据标题列对应插入
            //        var insertCount = bulk.Load();

            //        return true;
            //    }
            //   (dbHelper as DbHelper).Commit();
            //    (dbHelper as DbHelper).CloseConnection(cnn);
            //}
            //catch (Exception e)
            //{
            //    (dbHelper as DbHelper).Rollback();
            //    (dbHelper as DbHelper).CloseConnection(cnn);
            //    throw e;
            //}
            //finally
            //{
            //    File.Delete(tmpPath);
            //}
            //return false;
        }

        /// <summary>
        /// 生成插入数据的sql语句。
        /// </summary>
        /// <param name="database"></param>
        /// <param name="command"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        protected virtual string GenerateInserSql(SqlEntity sqle, DataTable table)
        {
            var names = new StringBuilder();
            var values = new StringBuilder();
            var types = new List<DbType>();
            var count = table.Columns.Count;
            table.Columns.CastToList<DataColumn>().ForEach(c =>
            {
                if (names.Length > 0)
                {
                    names.Append(",");
                }
                names.AppendFormat("{0}", FormatByQuote(c.ColumnName));
                types.Add(c.DataType.LookupDbType());
            });

            var i = 0;
            foreach (DataRow row in table.Rows)
            {
                if (i > 0)
                {
                    values.Append(",");
                }
                values.Append("(");
                for (var j = 0; j < count; j++)
                {
                    if (j > 0)
                    {
                        values.Append(", ");
                    }
                    var isStrType = IsStringType(types[j]);
                    var parameterName = string.Format("{0}p_{1}_{2}", ParamKeyword, i, j);
                    //var parameterName = ""; CreateParameter(isStrType, types[j], row[j], ParamKeyword, i, j);
                    if ((isStrType && row[j].ToString().IndexOf('\'') != -1) || types[j] == DbType.DateTime)
                    {
                        sqle.AddParameter(parameterName, row[j], types[j]);
                        values.Append(parameterName);
                    }
                    else if (isStrType)
                    {
                        values.AppendFormat("'{0}'", row[j]);
                    }
                    else
                    {
                        values.Append(row[j]);
                    }
                }
                values.Append(")");
                i++;
            }
            return string.Format("INSERT INTO {0}({1}) VALUES {2}", FormatByQuote(table.TableName), names, values);
        }


        ///// <summary>
        ///// 创建参数。
        ///// </summary>
        ///// <param name="provider"></param>
        ///// <param name="isStrType"></param>
        ///// <param name="dbType"></param>
        ///// <param name="value"></param>
        ///// <param name="parPrefix"></param>
        ///// <param name="row"></param>
        ///// <param name="col"></param>
        ///// <returns></returns>
        //private DbParameter CreateParameter(bool isStrType, DbType dbType, object value, string parPrefix, int row, int col)
        //{
        //    //如果生成全部的参数，则速度会很慢，因此，只有数据类型为字符串(包含'号)和日期型时才添加参数
        //    if ((isStrType && value.ToString().IndexOf('\'') != -1) || dbType == DbType.DateTime)
        //    {
        //        var name = string.Format("{0}p_{1}_{2}", parPrefix, row, col);
        //        var parameter = _dbProviderFactory.Value.CreateParameter();
        //        parameter.ParameterName = name;
        //        parameter.Direction = ParameterDirection.Input;
        //        parameter.DbType = dbType;
        //        parameter.Value = value;
        //        return parameter;
        //    }
        //    return null;
        //}
        #endregion

        #region DDL
        #region DDL sql
        protected override string CreateDataBaseSql
        {
            get
            {
                return "CREATE DATABASE {0} CHARACTER SET utf8 COLLATE utf8_general_ci ";
            }
        }
        protected override string AddPrimaryKeySql
        {
            get
            {
                return "ALTER TABLE {0} ADD PRIMARY KEY({2}) /*{1}*/";
            }
        }
        protected override string AddColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} ADD {1} {2}{3} {4} {5} {6}";
            }
        }
        protected override string AlterColumnToTableSql
        {
            get
            {
                // return "ALTER TABLE {0} ALTER COLUMN {1} {2}{3} {4} {5} {6}";
                return "alter table {0} change  column {1} {1} {2}{3} {4} {5} {6}";
            }
        }
        protected override string BackupDataBaseSql
        {
            get
            {
                return "mysqldump.exe  {0} -uroot -p > {1}  ";
            }
        }
        protected override string CreateTableSql
        {
            get
            {
                return "CREATE TABLE {0}(\r\n{1} $PrimaryKey)";
            }
        }
        protected override string CreateTableColumn
        {
            get
            {
                return "{0} {1}{2} {3} {4} {5}";
            }
        }
        protected override string TruncateTableSql
        {
            get
            {
                return "TRUNCATE TABLE {0}";
            }
        }
        protected override string BackupTableSql
        {
            get
            {
                return "Create table {1} (Select * from {2} LIMIT 0,{0})";
            }
        }
        protected override string DropTableSql
        {
            get
            {
                return "DROP TABLE {0}";
            }
        }
        protected override string DropColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} DROP COLUMN {1}";
            }
        }
        protected override string DropConstraintSql
        {
            get
            {
                return "ALTER TABLE {0} drop primary key;";
            }
        }
        protected override string RenameColumnSql
        {
            get
            {
                return "alter table {0} change  column {1} {2}";
            }
        }
        #endregion

        #region Check
        protected override string CheckSystemTablePermissionsSql
        {
            get
            {
                return "select 1 from Information_schema.columns limit 0,1";
            }
        }
        #endregion

        #region Scattered
        protected override string CreateTableNull
        {
            get
            {
                return "DEFAULT NULL";
            }
        }
        protected override string CreateTableNotNull
        {
            get
            {
                return "NOT NULL";
            }
        }
        protected override string CreateTablePirmaryKey
        {
            get
            {
                return "PRIMARY KEY";
            }
        }
        protected override string CreateTableIdentity
        {
            get
            {
                return "AUTO_INCREMENT";
            }
        }

        protected override string AddColumnRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string DeleteColumnRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string IsAnyColumnRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string AddTableRemarkSql
        {
            get
            {
                return "ALTER TABLE {0} COMMENT='{1}';";
            }
        }

        protected override string DeleteTableRemarkSql
        {
            get
            {
                return "ALTER TABLE {0} COMMENT='';";
            }
        }

        protected override string IsAnyTableRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string RenameTableSql
        {
            get
            {
                return "alter table {0} rename {1}";
            }
        }

        protected override string CreateIndexSql
        {
            get
            {
                return "CREATE {3} INDEX Index_{0}_{2} ON {0} ({1})";
            }
        }

        protected override string AddDefaultValueSql
        {
            get
            {
                return "ALTER TABLE {0} ALTER COLUMN {1} SET DEFAULT '{2}'";
            }
        }
        protected override string IsAnyIndexSql
        {
            get
            {
                return "SELECT count(*) FROM information_schema.statistics WHERE index_name = '{0}'";
            }
        }
        #endregion

        #region Methods
        /// <summary>
        ///by current connection string
        /// </summary>
        /// <param name="databaseDirectory"></param>
        /// <returns></returns>
        public override bool CreateDatabase(string databaseName, string databaseDirectory = null)
        {
            if (databaseDirectory != null)
            {
                if (!FileHelper.IsExistDirectory(databaseDirectory))
                {
                    FileHelper.CreateDirectory(databaseDirectory);
                }
            }
            var newDb = new DbHelper(this.dbHelper.DBName);
            var oldDatabaseName = Connection.Database;
            var connection = this.dbHelper.ConnectString;
            connection = connection.Replace(oldDatabaseName, "mysql");
            newDb.ConnectString = connection;
            if (!GetDataBaseList(newDb).Any(it => it.Equals(databaseName, StringComparison.CurrentCultureIgnoreCase)))
            {
                var sql = string.Format(CreateDataBaseSql, databaseName, databaseDirectory);
                var sqle = newDb.CreateSqlEntity(sql);
                newDb.Execute(sqle);
            }
            return true;
        }
        public override bool AddTableRemark(string tableName, string description)
        {
            string sql = string.Format(this.AddTableRemarkSql, this.GetTranslationTableName(tableName), description);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public override bool CreateTable(string tableName, List<DbColumnInfo> columns, bool isCreatePrimaryKey = true)
        {
            if (columns.HasValue())
            {
                foreach (var item in columns)
                {
                    if (item.Name.Equals("GUID", StringComparison.CurrentCultureIgnoreCase) && item.MaxLength == 0)
                    {
                        item.MaxLength = 10;
                    }
                }
            }
            string sql = GetCreateTableSql(tableName, columns);
            string primaryKeyInfo = null;
            if (columns.Any(it => it.IsPrimaryKey) && isCreatePrimaryKey)
            {
                primaryKeyInfo = string.Format(", Primary key({0})", string.Join(",", columns.Where(it => it.IsPrimaryKey).Select(it => this.GetTranslationColumnName(it.Name))));

            }
            sql = sql.Replace("$PrimaryKey", primaryKeyInfo);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public override bool AddRemark(DbTableInfo entity)
        {
            AddTableRemark(entity.TableName, entity.Description);
            List<DbColumnInfo> columns = entity.Columns.Where(it => it.IsIgnore == false).ToList();
            foreach (var item in columns)
            {
                if (item.Description != null)
                {
                    string sql = GetUpdateColumnSql(entity.TableName, item) + " " + (item.IsIdentity ? "AUTO_INCREMENT" : "") + " " + " COMMENT '" + item.Description + "'";
                    var sqle = this.dbHelper.CreateSqlEntity(sql);
                    this.dbHelper.Execute(sqle);
                }
            }
            return true;
        }
        protected override string GetCreateTableSql(string tableName, List<DbColumnInfo> columns)
        {
            List<string> columnArray = new List<string>();
            Check.Exception(columns.IsNullOrEmpty(), "No columns found ");
            foreach (var item in columns)
            {
                string columnName = item.Name;
                string dataSize = "";
                dataSize = GetSize(item);
                string dataType = item.Type;
                string nullType = item.IsNullable ? this.CreateTableNull : CreateTableNotNull;
                string primaryKey = null;
                string identity = item.IsIdentity ? this.CreateTableIdentity : null;
                string addItem = string.Format(this.CreateTableColumn, this.GetTranslationColumnName(columnName), dataType, dataSize, nullType, primaryKey, identity);
                columnArray.Add(addItem);
            }
            string tableString = string.Format(this.CreateTableSql, this.GetTranslationTableName(tableName), string.Join(",\r\n", columnArray));
            return tableString;
        }

        protected override string GetSize(DbColumnInfo item)
        {
            string dataSize = null;
            var isMax = item.MaxLength > 4000 || item.MaxLength == -1;
            if (isMax)
            {
                dataSize = "";
                item.Type = "longtext";
            }
            else if (item.MaxLength > 0 && item.DecimalDigits == 0)
            {
                dataSize = item.MaxLength > 0 ? string.Format("({0})", item.MaxLength) : null;
            }
            else if (item.MaxLength == 0 && item.DecimalDigits > 0)
            {
                item.MaxLength = 10;
                dataSize = string.Format("({0},{1})", item.MaxLength, item.DecimalDigits);
            }
            else if (item.MaxLength > 0 && item.DecimalDigits > 0)
            {
                dataSize = item.MaxLength > 0 ? string.Format("({0},{1})", item.MaxLength, item.DecimalDigits) : null;
            }
            return dataSize;
        }

        public override bool RenameColumn(string tableName, string oldColumnName, string newColumnName)
        {
            var columns = GetColumnInfosByTableName(tableName).Where(it => it.Name.Equals(oldColumnName, StringComparison.CurrentCultureIgnoreCase));
            if (columns != null && columns.Any())
            {
                var column = columns.First();
                var appendSql = " " + column.Type;
                if (column.MaxLength > 0 && column.Scale == 0)
                {
                    appendSql += string.Format("({0}) ", column.MaxLength);
                }
                else if (column.Scale > 0 && column.MaxLength > 0)
                {
                    appendSql += string.Format("({0},{1}) ", column.MaxLength, column.Scale);
                }
                else
                {
                    appendSql += column.IsNullable ? " NULL " : " NOT NULL ";
                }
                tableName = this.GetTranslationTableName(tableName);
                oldColumnName = this.GetTranslationColumnName(oldColumnName);
                newColumnName = this.GetTranslationColumnName(newColumnName);
                string sql = string.Format(this.RenameColumnSql, tableName, oldColumnName, newColumnName + appendSql);
                var sqle = this.dbHelper.CreateSqlEntity(sql);
                this.dbHelper.Execute(sqle);
                return true;
            }
            else
            {
                return false;
            }
        }
        public override bool AddDefaultValue(string tableName, string columnName, string defaultValue)
        {
            if (defaultValue == "''")
            {
                defaultValue = "";
            }
            if (defaultValue.ToLower().IsIn("now()", "current_timestamp"))
            {
                string template = "ALTER table {0} CHANGE COLUMN {1} {1} {3} default {2}";
                var dbColumnInfo = GetColumnInfosByTableName(tableName).First(it => it.Name.Equals(columnName, StringComparison.CurrentCultureIgnoreCase));
                string sql = string.Format(template, tableName, columnName, defaultValue, dbColumnInfo.Type);
                var sqle = this.dbHelper.CreateSqlEntity(sql);
                this.dbHelper.Execute(sqle);
                return true;
            }
            else if (defaultValue == "0" || defaultValue == "1")
            {
                string sql = string.Format(AddDefaultValueSql.Replace("'", ""), tableName, columnName, defaultValue);
                var sqle = this.dbHelper.CreateSqlEntity(sql);
                this.dbHelper.Execute(sqle);
                return true;
            }
            else
            {
                return base.AddDefaultValue(tableName, columnName, defaultValue);
            }
        }
        public override bool IsAnyConstraint(string constraintName)
        {
            throw new NotSupportedException("MySql IsAnyConstraint NotSupportedException");
        }
        public override bool BackupDataBase(string databaseName, string fullFileName)
        {
            Check.ThrowNotSupportedException("MySql BackupDataBase NotSupported");
            return false;
        }

        #endregion
        #endregion
    }
}
